package dao.impl;

import com.mysql.cj.util.StringUtils;
import dao.SmbmsBillDao;
import pojo.SmbmsBill;
import pojo.SmbmsUser;
import utils.BaseDao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class SmbmsBillDaoImpl extends BaseDao implements SmbmsBillDao {
    //查询账单信息并支持模糊查询支持供应商查询并支持付款情况查询并分页
    @Override
    public List<SmbmsBill> queryBillByList(String productname, int ispayment, int currentPageNo, int pageSize,int providerId) throws Exception {
        List<SmbmsBill> list=new ArrayList<SmbmsBill>();
        StringBuffer sql=new StringBuffer();
        List<Object> obj=new ArrayList<Object>();
        sql.append("select smbms_bill.*,smbms_provider.proName as providerName from smbms_bill inner join smbms_provider on smbms_bill.providerId=smbms_provider.id");
        if(!StringUtils.isNullOrEmpty(productname)){
            sql.append(" and smbms_bill.productName like ?");
            obj.add("%"+productname+"%");
        }
        if(ispayment>0){
            sql.append(" and smbms_bill.isPayment=?");
            obj.add(ispayment);
        }
        if(providerId>0){
            sql.append(" and smbms_bill.providerId=?");
            obj.add(providerId);
        }

//分页
        sql.append(" order by smbms_bill.creationDate desc limit ?,?");
        obj.add((currentPageNo-1)*pageSize);
        obj.add(pageSize);
        Object[] params=obj.toArray();
        System.out.println(productname);
        System.out.println("sql:"+sql.toString()+"========================");
        System.out.println(params+"=====================");
        ResultSet resultSet = executeSQL(sql.toString(), params);
        while(resultSet.next()){
            SmbmsBill smbmsBill=new SmbmsBill();
            smbmsBill.setId(resultSet.getLong("id"));
            smbmsBill.setBillCode(resultSet.getString("billCode"));
            smbmsBill.setProductName(resultSet.getString("productName"));
            smbmsBill.setProductDesc(resultSet.getString("productDesc"));
            smbmsBill.setProductUnit(resultSet.getString("productCount"));
            smbmsBill.setTotalPrice(resultSet.getString("totalPrice"));
            smbmsBill.setIsPayment(resultSet.getInt("isPayment"));
            smbmsBill.setCreatedBy(resultSet.getLong("createdBy"));
            smbmsBill.setCreationDate(resultSet.getDate("creationDate"));
            smbmsBill.setModifyBy(resultSet.getLong("modifyBy"));
            smbmsBill.setModifyDate(resultSet.getDate("modifyDate"));
            smbmsBill.setProviderId(resultSet.getInt("providerId"));
//把对象放在集合中
            list.add(smbmsBill);
        }
        return list;
    }

    //总条数
    @Override
    public int getproviderCount(String productname,int ispayment) throws Exception {
        int count=0;
        StringBuffer sql=new StringBuffer();
        List<Object> obj=new ArrayList<Object>();
        sql.append("select count(*) from smbms_bill u inner join smbms_provider r on u.providerId=r.id");
        if(!StringUtils.isNullOrEmpty(productname)){
            sql.append(" and u.productName like ?");
            obj.add("%"+productname+"%");
        }
        if(ispayment>0){
            sql.append(" and u.isPayment=?");
            obj.add(ispayment);
        }
        Object[] params=obj.toArray();
        System.out.println(sql+"==========================");
        ResultSet resultSet = executeSQL(sql.toString(), params);
        while(resultSet.next()){
            count=resultSet.getInt(1);
        }
        return count;
    }

    //通过id查询用户信息
    @Override
    public SmbmsBill queryBillById(Long id) throws Exception {
        SmbmsBill smbmsBill=new SmbmsBill();
        String sql="select u.*,r.proName as providerName from smbms_bill u inner join smbms_provider r on u.providerId=r.id and u.id=?";
        Object[] params={id};
        ResultSet resultSet = executeSQL(sql, params);
        while(resultSet.next()){
            smbmsBill.setId(resultSet.getLong("id"));
            smbmsBill.setBillCode(resultSet.getString("billCode"));
            smbmsBill.setProductName(resultSet.getString("productName"));
            smbmsBill.setProductUnit(resultSet.getString("productUnit"));
            smbmsBill.setProductCount(resultSet.getString("productCount"));
            smbmsBill.setTotalPrice(resultSet.getString("totalPrice"));
            //供应商id
            smbmsBill.setProviderId(resultSet.getInt("providerId"));
            smbmsBill.setIsPayment(resultSet.getInt("isPayment"));
        }
        return smbmsBill;
    }

    @Override
    public int updateBill(SmbmsBill smbmsBill) throws Exception {
        int count=0;
        String sql="update smbms_bill set billCode=?,productName=?,productUnit=?,productCount=?,totalPrice=?,providerId=?,isPayment=? where id=?";
        Object[] params= {smbmsBill.getBillCode(),smbmsBill.getProductName(),smbmsBill.getProductUnit(),
                smbmsBill.getProductCount(),smbmsBill.getTotalPrice(),smbmsBill.getProviderId(),smbmsBill.getIsPayment(),smbmsBill.getId()};
        count= executeUpdate(sql, params);
        return count;
    }

    @Override
    public int deleteBill(Long id) throws Exception {
        int count=0;
        String sql="delete from smbms_bill where id=?";
        Object[] params={id};
        count = executeUpdate(sql, params);
        return count;
    }

    @Override
    public int addBill(SmbmsBill smbmsBill) throws Exception {
        int count=0;
        String sql="insert into smbms_bill(billCode, productName,productUnit, productCount, totalPrice," +
                " isPayment,createdBy,creationDate,providerId)values(?,?,?,?,?,?,?,?,?)";
        Object[] params= {smbmsBill.getBillCode(),smbmsBill.getProductName(),smbmsBill.getProductUnit(),
                smbmsBill.getProductCount(), smbmsBill.getTotalPrice(),smbmsBill.getIsPayment(),smbmsBill.getCreatedBy(),
                smbmsBill.getCreationDate(),smbmsBill.getProviderId()};
        count = executeUpdate(sql, params);
        return count;

    }

}


